package edu.hhu.dynamic.table;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class TableDao {
	public String getData(String nodeStr,String fieldStr,String s_date,String e_date){
//		System.out.println("|"+nodeStr+"|");
//		System.out.println("|"+fieldStr+"|");
//		System.out.println("|"+s_date+"|");
//		System.out.println("|"+e_date+"|");
		
		String sql=getSql(nodeStr, fieldStr, s_date, e_date);
		//System.out.println(sql);
		//System.out.println(Db.use("mysql"));
		
		List<Record> data = Db.use("mysql").find(sql);
		//System.out.println(data);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			jsonList.add(record.getColumns());
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		//System.out.println(i + "||" + json);
		
		return json;
	}
	
	
	
	//根据fieldStr获取 返回sql语句
	public String getSql(String nodeStr,String fieldStr,String s_date,String e_date){
		String sql="";
		String tableName=getTablename(fieldStr);
		String[] tablename=tableName.split(";");
		
		String[] nodes=nodeStr.split(",");
		String nodesCon="(";
		for(int i=0;i<nodes.length;i++){
			nodesCon+=" WELLNAME='"+nodes[i]+"' or";
		}
		nodesCon=nodesCon.substring(0,nodesCon.length()-3);
		nodesCon=nodesCon+")";
		if(tablename.length==1){
			String[] temp=tablename[0].split(":");
			sql="SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ,"+temp[1]+" FROM "+temp[0]+"  WHERE SCRQ BETWEEN '"+s_date+"' AND '"+e_date
					+"' and "+nodesCon+";";
			//System.out.println(sql);
		}
		if(tablename.length==2){
			String[] temp1=tablename[0].split(":");
			String sql1="SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ,"+temp1[1]+" FROM "+temp1[0]+"  WHERE SCRQ BETWEEN '"+s_date+"' AND '"+e_date
					+"' and "+nodesCon;
			String[] temp2=tablename[1].split(":");
			String sql2="SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ,"+temp2[1]+" FROM "+temp2[0]+"  WHERE SCRQ BETWEEN '"+s_date+"' AND '"+e_date
					+"' and "+nodesCon;
			sql="select t.WELLNAME,DATE_FORMAT(t.SCRQ,'%Y-%m-%d') SCRQ,"+fieldStr+" from ("+sql1+") t left join "
					+"("+sql2+") s on (t.WELLNAME=s.WELLNAME and t.SCRQ=s.SCRQ);";
			//System.out.println(sql);
		}
		return sql;
	}
	
	
	
	//根据fieldStr获取  表名cjbbrsctrue,cjbbtjtrue
	public String getTablename(String fieldStr){
		String[] field=fieldStr.split(",");
		String sql;
		String table1="CJBBRSCtrue:";
		String table2="CJBBTJtrue:";
		int num=field.length;
		StringBuilder sql_tem=new StringBuilder("select tablename,fieldcode from dictionary where ");
		for(int i=0;i<num;i++){
			sql_tem.append(" fieldcode='"+field[i]+"' or");
		}
		sql=sql_tem.toString();
		sql=sql.substring(0, sql.length()-3);
		int num1=0;
		int num2=0;
		List<Record> data=Db.use("mysql").find(sql);
		for(Record record:data){

			String tablename=record.get("tablename");
			if(tablename.equals("CJBBRSCtrue")){
				num1++;
				table1+=record.get("fieldcode")+",";
			}
            if(tablename.equals("CJBBTJtrue")){
            	num2++;
            	table2+=record.get("fieldcode")+",";
			}
		}
		table1=table1.substring(0,table1.length()-1);
		table2=table2.substring(0,table2.length()-1);
		if(num1>0&&num2>0){
			return table1+";"+table2;
		}else if(num1>0){
			return table1+";";
		}else{
			return table2+";";
		}
	}

    public List getHaeder(){
    	String sql="select fieldcode,concat(fieldname,'（',unitname,'）') fieldname from dictionary ;";
    	List<Record> data = Db.use("mysql").find(sql);
    	List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			jsonList.add(record.getColumns());
			i++;
		}
		String json = JSON.toJSONString(jsonList);
//		System.out.println(i + "||" + json);
		
		return jsonList;

    }


}
